package org.weso.lodin.dao;

import java.io.IOException;
import java.io.InputStream;
import java.util.LinkedList;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.weso.lodin.exceptions.LodinModelException;
import org.weso.lodin.generated.CountryEnum;
import org.weso.lodin.generated.Dataset;
import org.weso.lodin.generated.TypeTable;
import org.weso.lodin.generated.TypeValues;
import org.weso.lodin.to.RankingScoreObservationTO;
import org.weso.lodin.utils.POIUtils;
import org.weso.lodin.utils.StatusUtils;

public class RankingScoresObservationDAOImpl implements RankingScoreObservationDAO {

	protected static Logger logger = Logger.getLogger(RankingScoresObservationDAOImpl.class);
	private HSSFWorkbook wb;

	public RankingScoresObservationDAOImpl(InputStream data){
		try {			
			this.wb = new HSSFWorkbook(data);
			logger.info("Loading workbook");
			data.close();			
		} catch (IOException e) {
			throw new LodinModelException(e, "Loading workbook");
		}
	}
	

	public List<RankingScoreObservationTO> getObservations(List<Dataset> datasets) {
		List observations = new LinkedList<RankingScoreObservationTO>();		
		for(Dataset dataset:datasets){
			observations.addAll(extractObservationsByDataset(dataset));
		}
		return observations;
	}

	


	private List<RankingScoreObservationTO> extractObservationsByDataset(Dataset dataset) {
		List<RankingScoreObservationTO> observations = new LinkedList<RankingScoreObservationTO>();
		HSSFSheet sheet = wb.getSheet(dataset.getId());
		FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
		logger.info("Loading observations from sheet: "+dataset.getId()+" "+dataset.getIndicator());
		for(TypeTable tableType:dataset.getTable()){
			int start = tableType.getRowStart().intValue();
			int end = tableType.getRowEnd().intValue();
			for(int rowIndex = start-1; rowIndex<end; rowIndex++){
				HSSFRow row = sheet.getRow(rowIndex);
				if (row == null) {
					continue;
				}				
				List<TypeValues> values = tableType.getValues();	
				for(TypeValues item:values){	
					int valueCol = encode(item.getValueCol());
					//String title = item.getTitle();	
					String dataType = item.getDatatype();
					String isoCode = item.getCodeCol();
					String countryCol = item.getCountryCol();
					String countryValue = "";
					CountryEnum countryType = null ;
					String countryAltValue = "";
					CountryEnum countryAltType = CountryEnum.NAME;	
					//1-0, 0-1, 1-1
					if(isoCode!= null && 
							!isoCode.equalsIgnoreCase("") && 
							(countryCol==null || countryCol.equalsIgnoreCase(""))){
						countryValue = POIUtils.extractValue(
								row.getCell(encode(isoCode)), 
								evaluator);
						countryType = (isoCode.length()==2)?CountryEnum.ISO_CODE_2:CountryEnum.ISO_CODE_3;
					}else if(countryCol!= null && 
							!countryCol.equalsIgnoreCase("") && 
							(isoCode==null || isoCode.equalsIgnoreCase(""))){
						countryValue = POIUtils.extractValue(
								row.getCell(encode(countryCol)), 
								evaluator);
						countryType = CountryEnum.NAME;
					}else if(countryCol!= null && 
							!countryCol.equalsIgnoreCase("") && 
							isoCode!=null && 
							!isoCode.equalsIgnoreCase("")){
						countryValue = POIUtils.extractValue(
								row.getCell(encode(isoCode)), 
								evaluator);
						countryType = (isoCode.length()==2)?CountryEnum.ISO_CODE_2:CountryEnum.ISO_CODE_3;
						countryAltValue = POIUtils.extractValue(
								row.getCell(encode(countryCol)), 
								evaluator);
						countryAltType = CountryEnum.NAME;
					}
							
					String value = POIUtils.extractValue(row.getCell(valueCol), evaluator);
					String year = ""+item.getYear().getYear();
					StatusUtils status = StatusUtils.NORMAL;
					
					RankingScoreObservationTO observation = new RankingScoreObservationTO(
							"",
							dataType,
							countryValue, 
							countryType, 
							countryAltValue, 
							countryAltType, 
							value,
							year);
					observation.setDataset(dataset);
					observation.setStatus(status);
					observations.add(observation);
				
				}
				
//				int countryColAlt = -1;
//				try{
//					countryColAlt = encode(values.getCountryColAlt().charAt(0));
//				}catch(NullPointerException e){
//					countryColAlt = -1;
//				}
			
			}
		}
		return observations;
	}


	

		public static int encode(String col){
		//A->0 [A-Z]->[0-26]
		//B->1
		//'X'->col.charAt(0)-(int) 'A'
		//AA->26->
		//AB->27
		//AAA->53=26*26+1
		//AAB->54=26*26+2
		//encode(col) = (col.length-1)+(col.charAt(col.length()-1)-(int)'A')+(col.length()-1)		
		int offset = (int)'Z'-(int)'A';
		if(col.length()==1){
			return col.charAt(0)-(int)'A'; 	
		}else{
			return (offset *(col.length()-1))+((col.charAt(col.length()-1)-(int)'A'))+(col.length()-1);
		}
		}
		
		



}
